package app.database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.OracleResultSet;
import oracle.jdbc.pool.OracleDataSource;

/**
 *
 * @author Lukáš Marek
 */
public class MainDatabase
{
	private String username;
	private String password;
	private String address;
	private int port;
	private String sid;
	
	public Connection conn = null;
	private boolean onceConnected = false;
	private boolean lastLoginState = false;
	
	public String getURL()
	{
		String url = "jdbc:oracle:thin:@";
		url += address + ":" + port;
		if(!sid.equals("")) url += ":" + sid;
		return url;
	}
	
	public void getDBConnection() throws SQLException
	{
		OracleDataSource ds;
		ds = new OracleDataSource();
		ds.setURL(getURL());
		conn = ds.getConnection(username, password);
	}
 
	/**
		* Funkce pro pripojeni se k databazi
		* @param username uzivatelske jmeno
		* @param password heslo
		* @param address  Adresa databaze
		* @param port pouzty port
		* @param SID sid
		* @return logicka hodnota, zda se pripojeni zdarilo
		*/
	public boolean Login(String username, String password, String address, int port, String SID)
	{
		this.username = username;
		this.password = password;
		this.address = address;
		this.port = port;
		this.sid = SID;
		
		System.out.println(getURL());
		try
		{
			getDBConnection();
		}
		catch (SQLException ex)
		{
			conn.close();
			conn=null;
			System.err.println(ex.getMessage());
		}
		finally
		{  
			if(this.onceConnected == false) this.onceConnected = !(conn==null);
			if(conn != null)
			{
				this.onceConnected = true;
				System.out.println("Pripojeno");
			}
			else
				System.out.println("Pripojeni se nezdarilo");

			this.lastLoginState = !(conn == null);
			return this.lastLoginState;
		}
	}
	
	/**
		* Odpojeni od databaze
		* @throws SQLException 
		*/
	public void Logout()
	{
		if (this.isLogged())
		{
			try
			{
				conn.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}
	}
		
	/**
		* Testovani pripojeni
		* @return logicka hodnota, zda je udrzeno spojeni s databazi
		*/
	public boolean isLogged()
	{
		try
		{
			if (this.conn == null || !conn.isValid(5))
			{
				this.conn = null; 
			}
		}
		catch (SQLException ex)
		{
			System.err.println(ex.getMessage());
			return false;
		}
		return !(this.conn == null);
	}
	
	/**
		* Pokud bylo spojeni s databazi preruseno, pokusi se jej znova
		* navazat pomoci drive uspesne pouzitych prihlasovacich udaju
		* @return Logicka hodnota, zda je nyni spojeni navazano
		* @throws SQLException 
		*/
	public boolean reconnect() throws SQLException
	{
		if(!isLogged())
		{
			if(!this.onceConnected) return false;
			else return Login(username, password, address, port, sid);  
		}
		else
			return true;
	}

/**
	* Jednoduchy sql prikaz, nevracejici zadna data z databaze
	* Pouziti pro SQL prikazy INSERT, DELETE, a UPDATE.
	* @param sqlstatement sql prikaz, ktery se ma vykonat
	* @return pocet radku databaze, ktere byly ovlivneny
	* @throws SQLException  
	*/
	public int executeUpdate(String sqlstatement) throws SQLException
	{
		int res=0;
		try 
		(Statement stmt = this.conn.createStatement())
		{
			res=stmt.executeUpdate(sqlstatement);
		} 
		catch (SQLException e ) 
		{  
			if(!this.isLogged())//neni pripojeni
					if(this.reconnect())//pokusi se znovupripojit
							return this.executeUpdate(sqlstatement); //a cele znova
		}
		finally
		{
			return res;
		}        
	}
	
/**
	* Dotazy do databaze, vracejici jediny resultset.
	* @param query sql dotaz
	* @return v pripade uspechu vraci resultSet s odpovedi, pri chybe null
	*        (na resultSet je treba pred prvnim cteni zavolat ResultSet.next())
	* @throws SQLException  
	*/
	public ResultSet executeQuery(String query) throws SQLException 
	{
		OracleResultSet res=null;
		Statement stmt;
		try 
		{
			stmt = this.conn.createStatement();
			res = (OracleResultSet)stmt.executeQuery(query);
		} 
		catch (SQLException e ) //doslo k chybe
		{  
			if(!this.isLogged())//neni pripojeni
					if(this.reconnect())//pokusi se znovupripojit
							return this.executeQuery(query); //a cele znova    
		}   
		finally 
		{
			return res;
		}        
	}
}